package com.sds.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.sds.fw.dao.AbDao;
import com.sds.shop.vo.OrderVO;
import com.sds.shop.vo.ProductVO;

public class OrderDAO extends AbDao {
	private static OrderDAO dao = new OrderDAO();
	public static OrderDAO getInstance(){
		return dao;
	}
	
	public void createOrder(OrderVO vo, HashMap<String, ProductVO> list){
		
		Connection conn = null;
		PreparedStatement ps = null;
		String ordNo="ORD"+ String.format("%09d", dao.getOrderList("","").size()+1);
		
		try {
			conn = connect();
			System.out.println(vo.getMemID());
			StringBuffer query = new StringBuffer();
			query.append("insert into tb_order(ord_no, mem_id, ord_amt, rcvr_nm, rcvr_tel_no, rcvr_addr, ");
			query.append("dlv_corp_no, dlv_req, ord_stat_cd, ord_dtm)");
			query.append("values (?,?,?,?,?,?,?,?,?,to_char(sysdate, 'YYYYMMDDHH24MISS'))");
			
			ps = conn.prepareStatement(query.toString());
			ps.setString(1, ordNo);
			ps.setString(2, vo.getMemID());
			ps.setString(3, vo.getOrderprice());
			ps.setString(4, vo.getName());
			ps.setString(5, vo.getTelno());
			ps.setString(6, vo.getAddress());
			ps.setString(7, vo.getDlv_corp_no());
			ps.setString(8, vo.getDlv_req());
			ps.setString(9, vo.getOrd_stat_cd());
			int c = ps.executeUpdate();
			ps.close();
			
			if(c>0) {
				Iterator<ProductVO> iter = list.values().iterator();
				
				while (iter.hasNext()) {
					String q = "insert into tb_order_product(ord_no, prd_no, qty) " +
							"values (?,?,?)";
					
					ps=conn.prepareStatement(q);
										
					ProductVO gvo = (ProductVO) iter.next();
					ps.setString(1, ordNo);
					ps.setString(2, gvo.getPrdNo());			
					ps.setInt(3, gvo.getBasketCount());
					ps.executeUpdate();
					ps.close();
				}
			}
			conn.close();
			
		} catch (Exception ex) {
			System.out.println("insertOrder exception : " + ex);
			close(conn, ps);
		}
	}
	
public void updateOrder(String ord_no){
		
		Connection conn = null;
		PreparedStatement ps = null;
		
		try {
			conn = connect();
			StringBuffer query = new StringBuffer();
			query.append("update tb_order set ord_stat_cd='B01' ");
			query.append("where ord_no= ? ");
			
			ps = conn.prepareStatement(query.toString());
			ps.setString(1, ord_no);
			ps.executeUpdate();
			ps.close();
			conn.close();
			
		} catch (Exception ex) {
			System.out.println("updateOrder exception : " + ex);
			close(conn, ps);
		}
	}
	
	public OrderVO getOrder(String ord_no){
		OrderVO ovo = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("SELECT ord_no, mem_id, ord_amt, rcvr_nm, rcvr_tel_no, ");
			query.append("rcvr_addr, dlv_corp_no, dlv_req, ord_stat_cd, ord_dtm ");
			query.append("FROM tb_order ");
			query.append("WHERE ord_no = ? "); 
			query.append("ORDER BY ord_dtm DESC "); 
			ps = conn.prepareStatement( query.toString() );
			ps.setString(1, ord_no);				 

			rs = ps.executeQuery();
			while(rs.next()){
				ovo = new OrderVO();
			    ovo.setOrd_no(rs.getString(1));
			    ovo.setMemID(rs.getString(2));
			    ovo.setOrderprice(rs.getString(3));
			    ovo.setName(rs.getString(4));
			    ovo.setTelno(rs.getString(5));
			    ovo.setAddress(rs.getString(6));
				ovo.setDlv_corp_no(rs.getString(7));
				ovo.setDlv_req(rs.getString(8));
				ovo.setOrd_stat_cd(rs.getString(9));
				ovo.setOrd_dtm(rs.getString(10));
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			System.out.println("getOrderList exception : " + ex);
			close(conn, ps, rs);
		} 
		
		return ovo;
	}
	
	public ArrayList<OrderVO> getOrderList(String key, String value){
		ArrayList<OrderVO> list = new ArrayList<OrderVO>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try{
			conn = connect();

			StringBuffer query = new StringBuffer();
			query.append("SELECT ord_no, mem_id, ord_amt, rcvr_nm, rcvr_tel_no, ");
			query.append("rcvr_addr, dlv_corp_no, dlv_req, ord_stat_cd, ord_dtm ");
			query.append("FROM tb_order ");
						
			if( "ALL".equals( value ) || value == null || value.equals("")){ 
				query.append("ORDER BY ord_dtm DESC "); 
				ps = conn.prepareStatement( query.toString() );				
			} else if (key.equals("mem_id")) {
				query.append("WHERE mem_id = ? "); 
				query.append("ORDER BY ord_dtm DESC "); 
				ps = conn.prepareStatement( query.toString() );
				ps.setString(1, value);				 
			} else if (key.equals("code"))	{
				query.append("WHERE ord_stat_cd = ? "); 
				query.append("ORDER BY ord_dtm DESC "); 
				ps = conn.prepareStatement( query.toString() );
				ps.setString(1, value);	
			}

			rs = ps.executeQuery();
			while(rs.next()){
				OrderVO gvo = new OrderVO();
			    gvo.setOrd_no(rs.getString(1));
			    gvo.setMemID(rs.getString(2));
			    gvo.setOrderprice(rs.getString(3));
			    gvo.setName(rs.getString(4));
			    gvo.setTelno(rs.getString(5));
			    gvo.setAddress(rs.getString(6));
				gvo.setDlv_corp_no(rs.getString(7));
				gvo.setDlv_req(rs.getString(8));
				gvo.setOrd_stat_cd(rs.getString(9));
				gvo.setOrd_dtm(rs.getString(10));
			    
				list.add(gvo);
			}
			
			rs.close();
			ps.close();
			conn.close();
			
		}catch(Exception ex){
			System.out.println("getOrderList exception : " + ex);
			close(conn, ps, rs);
		} 
		
		return list;
	} // getGoodsList
	
	/*
	public static void main(String args[]){
		OrderDAO dao = OrderDAO.getInstance();
		ArrayList<OrderVO> list = dao.getOrderList("");
		Iterator<OrderVO> iter = list.iterator();
		while (iter.hasNext()) {
			Object tmp = (Object) iter.next();
			System.out.println(tmp);
		}
	}
	*/
	

}
